import pdfplumber
import glob
import json
import re
import os
import win32com.client
import shutil
from openpyxl import load_workbook
from openpyxl import Workbook


def pdf_extractor():
    doc = []
    ward = []
    result = {}
    for each_pdf in glob.glob('.\\pdf\\*.pdf'):
        pdf = pdfplumber.open(each_pdf)
        for page in pdf.pages:

            lines = page.extract_table(
                table_settings={"vertical_strategy": "lines_strict"})
            for idx, line in enumerate(lines):
                if idx > 0:
                    doc.append(line[8])
                    ward.append(line[6])

    result['doc'] = doc
    result['ward'] = ward
    with open('hospital_coding.json', 'w', encoding='utf-8') as f:
        json.dump(result, f, ensure_ascii=False)


def excel_extractor():
    wb = load_workbook('.\\excel_files\\1.xlsx')
    ws = wb.active

    doc = []
    ward = []
    for idx, each in enumerate(ws['j']):
        if idx > 0:
            doc.append(each.value)
    for idx, each in enumerate(ws['g']):
        if idx > 0:
            ward.append(each.value)
    result = {'doc': doc, 'ward': ward}
    with open('hospital_decoding.json', 'w', encoding='utf-8') as f:
        json.dump(result, f, ensure_ascii=False)


def generate_decoding():
    with open('hospital_decoding.json', 'r', encoding='utf-8') as f_code:
        code_json = json.load(f_code)

    with open('hospital_coding.json', 'r', encoding='utf-8') as f_name:
        name_json = json.load(f_name)

    doc_code = code_json['doc']
    ward_code = code_json['ward']

    doc_name = name_json['doc']
    ward_name = name_json['ward']

    doc_decode = dict(set(zip(doc_code, doc_name)))
    ward_decode = dict(set(zip(ward_code, ward_name)))

    decoding = {'doc': doc_decode, 'ward': ward_decode}
    with open('decoding.json', 'w', encoding='utf-8') as f:
        json.dump(decoding, f, ensure_ascii=False)


def decode_file(excel_file):
    wb_read = load_workbook(excel_file)
    ws = wb_read.active
    with open('decoding.json', 'r', encoding='utf-8') as f:
        decoding = json.load(f)
    for each in ws['g']:
        ward_name = decoding['ward'].setdefault(each.value, '')
        ward_name = re.sub(u'\u0000', "", ward_name)
        each.value = ward_name if ward_name else each.value
    for each in ws['j']:
        doc_name = decoding['doc'].setdefault(each.value, '')
        each.value = doc_name if doc_name else each.value
    wb_read.save('decoded.xlsx')
    # wb_create = Workbook()
    # sheet = wb_create.active
    # sheet['a'] = ws['b']
    # sheet['b'] = ws['c']
    # sheet['c'] = ws['d']
    # sheet['d'] = ws['s']
    # sheet['e'] = ws['g']
    # sheet['f'] = ws['j']
    # sheet['g'] = ws['m']
    # sheet['h'] = ws['n']
    # wb_create.save('decoded.xlsx')


def xls_to_xlsx(dirname):
    if not os.path.exists('xlsx_dir'):
        os.makedirs('xlsx_dir')
    else:
        shutil.rmtree('xlsx_dir')
        os.makedirs('xlsx_dir')
    for each in glob.glob("%s\\*.xls" % dirname):
        excel_exe = win32com.client.gencache.EnsureDispatch('Excel.Application')
        wb = excel_exe.Workbooks.Open(each)
        wb.SaveAs("E:\\cbl\\stuff\\pdfreader\\xlsx_dir\\%sx" % os.path.basename(each),
                  FileFormat=51)
        wb.Close()
        excel_exe.Application.Quit()


if __name__ == '__main__':
    wb = load_workbook('.\\xlsx_dir\\2.xlsx')
    ws = wb.active

    key_row = ['a', 'e', 'i', 'q']

    result = []
    date_= ''
    doc = ''
    for idx, each in enumerate(ws['e']):
        if each.value:
            if ws[f'a{idx + 1}'].value:
                date_ = ws[f'a{idx + 1}'].value

            else:
                ws[f'a{idx + 1}'].value = date_
            if ws[f'q{idx + 1}'].value:
                doc = ws[f'q{idx + 1}'].value

            else:
                ws[f'q{idx + 1}'].value = doc
            result.append([each.value,
                           ws[f'a{idx+1}'].value,
                           ws[f'i{idx+1}'].value if ws[f'i{idx+1}'].value else '0',
                           ws[f'q{idx+1}'].value,
                           ])

    print(result)